package com.jietu.mapper;

/**
 * @author LQ&LYPlq
 * @ClassName VoMapper
 * @description: TODO
 * @date 2024年12月16日
 * @version: 1.0
 */

import com.jietu.entity.Vo;
import com.jietu.entity.Vo1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    /**
     * 按照第二大类交易额排名
     */
    @Select("select  c2.name ,sum(oi.final_total_amount) `value` from order_info oi\n" +
            "LEFT JOIN order_detail od ON od.order_id=oi.id\n" +
            "LEFT JOIN sku_info si ON od.sku_id =si.id\n" +
            "LEFT JOIN base_category3 c3 ON si.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id= c2.id\n" +
            "GROUP BY c2.name order by `value`")
    List<Vo> getVoList();
    @Select("select  CONCAT(SUBSTR(birthday,3,1),'0后') name ,sum(oi.final_total_amount)  value  from   user_info   uo   left  join   order_info   oi   on     uo.id=oi.user_id  group   by    CONCAT(SUBSTR(birthday,3,1),'0后') ")
    List<Vo> getVoList2();

   @Select("select  yys `name` , sum(oi.final_total_amount) value    from   order_info   oi   where    yys  is not  null  group  by  oi.yys ")
    List<Vo> getVoList3();

    @Select(" select bt.name,\n" +
            "if(ui.gender='F','男','女') sex,\n" +
            "sum(oi.final_total_amount) `value`  from order_info oi\n" +
            "LEFT JOIN user_info ui ON oi.user_id=ui.id\n" +
            "LEFT JOIN order_detail od on od.order_id = oi.id\n" +
            "LEFT JOIN sku_info si on od.sku_id = si.id\n" +
            "LEFT JOIN base_trademark bt on si.tm_id = bt.id\n" +
            "GROUP BY bt.name,ui.gender   ")
    List<Vo1> getVoList4();
}
